﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;

namespace WcfServiceAfvanding.Code
{
    //[DataContract]
    public class Pumpestation
    {
        private const string connectionString = "Data Source=ealdb1.eal.local;User ID=ejl02_usr;Password=Baz1nga2;Initial Catalog=EJL02_DB;Trusted_connection=False";

        SqlCommand cmd = new SqlCommand ();
        SqlConnection con = new SqlConnection(connectionString);
        SqlDataReader datareader;

        int portnummer, pumpestationsId;
        string ipadresse, vandstand, netværksadresse;
        ObservableCollection<Pumpestation> minePumpstationer;

        #region Property
        //[DataMember]
        public string Netværksadresse
        {
            get { return netværksadresse; }
            set { netværksadresse = value; }
        }
        //[DataMember]
        public int Portnummer
        {
            get { return portnummer; }
            set { portnummer = value; }
        }
        //[DataMember]
        public int PumpestationsId
        {
            get { return pumpestationsId; }
            set { pumpestationsId = value; }
        }
        //[DataMember]
        public string Ipadresse
        {
            get { return ipadresse; }
            set { ipadresse = value; }
        }
        //[DataMember]
        public string Vandstand
        {
            get { return vandstand; }
            set { vandstand = value; }
        }

        #endregion

        public static ObservableCollection<Pumpestation> MinePumpestationer()
        {
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            Pumpestation pumpestation;

            cmd.Connection = con;
            cmd.CommandText = "SELECT * FROM Pumpestation";

            con.Open();
            SqlDataReader datareader = cmd.ExecuteReader();

            ObservableCollection<Pumpestation> pumpestationer = new ObservableCollection<Pumpestation>();

            // Gennemløb datareader (result set)
            while (datareader.Read() == true)
            {
                pumpestation = new Pumpestation();
                pumpestation.pumpestationsId = (int)datareader["pumpestationsId"];
                pumpestation.netværksadresse = ((string)datareader["netværksadresse"]);
                pumpestation.ipadresse = ((string)datareader["ipadresse"]);
                pumpestation.portnummer = ((int)datareader["portnummer"]);
                pumpestation.vandstand = ((string)datareader["vandstand"]);
                pumpestationer.Add(pumpestation);
            }
            con.Close();             // Luk forbindelsen

            return pumpestationer;
        }

        public void SletPumpestation(int pumpestationsId)
        {
            cmd.Connection = con;
            string sqlStr = "DELETE FROM Pumpestation WHERE pumpestationsId = '" + pumpestationsId + "'";

            cmd.CommandText = sqlStr;

            // sæt værdier ind i parametrene
            SqlParameter par = new SqlParameter("@pumpestationsId", SqlDbType.Int);
            par.Value = pumpestationsId;
            cmd.Parameters.Add(par); // gem parameteren i parameterlisten

            try
            {
                con.Open(); // Åben forbindelsen til databasen
                cmd.ExecuteNonQuery();
            }

            catch (SqlException e)
            {
            }

            finally  // eksekveres altid
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close(); // hvis connection var åben: så luk!!
                }
            }
        }

        public void OpretPumpstation()
        {
            cmd.Connection = con;
            string sqlStr = "INSERT INTO Pumpestation VALUES(@netværksadresse, @ipAdresse, @portnummer, @vandstand)";

            cmd.CommandText = sqlStr;
            cmd.Parameters.Clear();

            // Sæt værdier i parametre
            SqlParameter par = new SqlParameter("@pumpestationsId", SqlDbType.Int);
            par.Value = pumpestationsId;
            par.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(par); // gem parameteren i parameterlisten

            par = new SqlParameter("@netværksadresse", SqlDbType.VarChar);
            par.Value = netværksadresse;
            cmd.Parameters.Add(par); // gem parameteren i parameterlisten

            par = new SqlParameter("@ipAdresse", SqlDbType.VarChar);
            par.Value = ipadresse;
            cmd.Parameters.Add(par); // gem parameteren i parameterlisten

            par = new SqlParameter("@portnummer", SqlDbType.Int);
            par.Value = portnummer;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@vandstand", SqlDbType.VarChar);
            par.Value = vandstand;
            cmd.Parameters.Add(par);

            con.Open();
            cmd.ExecuteNonQuery();
            //pumpestationsId = (int.Parse(cmd.Parameters["@pumpestationsId"].Value.ToString())); // returner datatypen som er kundeId
            con.Close();
            //return pumpestationsId;
        }

        public void RetPumpstation(int pumpestationsId, int netværksadresse, string ipadresse, int portnummer, string vandstand)
        {
            cmd.Connection = con;
            datareader = null;
            cmd.CommandText = "RetPumpe";
            cmd.Parameters.Clear();

            SqlParameter par = new SqlParameter("@pumpestationsId", SqlDbType.Int);
            par.Value = pumpestationsId;
            par.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@netværksadresse", SqlDbType.Int);
            par.Value = netværksadresse;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@ipadresse", SqlDbType.VarChar);
            par.Value = ipadresse;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@portnummer", SqlDbType.Int);
            par.Value = portnummer;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@vandstand", SqlDbType.VarChar);
            par.Value = vandstand;
            cmd.Parameters.Add(par);

            try
            {
                con.Open(); // Åben forbindelsen til databasen
                cmd.ExecuteNonQuery();
                //return (int.Parse(sqlCommand.Parameters["@pumpestationsId"].Value.ToString()));
            }

            catch (SqlException e)
            {
                throw e;
            }

            finally  // eksekveres altid
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close(); // hvis connection var åben: så luk!!
                }
            }
        }
    }
}